package itheima02;

import org.junit.Test;
import utils.JDBCUtil;

import java.sql.*;

/*
    使用JDBC工具类完成查询



    步骤:

        1.使用JDBC工具类获取连接Connection对象
        2.获取执行SQL语句的Statement对象
        3.Statement对象执行sql语句
        4.关闭资源

        思考:
            能否将查询出来的每条记录,封装成一个Student对象呢?

 */
/*public class Demo03JDBCUtil {
    //查询一条记录
    @Test
    public void queryOne() {
        //定义变量
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.使用JDBC工具类获取连接Connection对象
            con = JDBCUtil.getConnection();



            //2.获取执行SQL语句的Statement对象
            stmt = con.createStatement();

            //定义sql语句
            String sql = "select * from scores where sname='宝强'";

            //3.Statement对象执行sql语句
            rs = stmt.executeQuery(sql);

            //处理结果集
            if(rs.next()) {
                Object sid = rs.getObject(1);
                Object score = rs.getObject(2);
                Object sname = rs.getObject(3);
                System.out.println("编号: "+sid+", 姓名: "+sname+", 成绩: "+score);
            } else {
                System.out.println("没有数据");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源
            JDBCUtil.release(con,stmt,rs);
        }
    }

    //查询所有记录
    @Test
    public void queryAll() {
        //定义变量
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.使用JDBC工具类获取连接Connection对象
            con = JDBCUtil.getConnection();



            //2.获取执行SQL语句的Statement对象
            stmt = con.createStatement();

            //定义sql语句
            String sql = "select * from scores";

            //3.Statement对象执行sql语句
            rs = stmt.executeQuery(sql);

            //处理结果集
            while(rs.next()) {
                Object sid = rs.getObject(1);
                Object score = rs.getObject(2);
                Object sname = rs.getObject(3);
                System.out.println("编号: "+sid+", 姓名: "+sname+", 成绩: "+score);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源
            JDBCUtil.release(con,stmt,rs);
        }
    }

}*/
//执行成功
//编号: 1, 姓名: 柳岩, 成绩: 120
//编号: 2, 姓名: wkr, 成绩: 100
//编号: 3, 姓名: yny, 成绩: 150
//编号: 4, 姓名: wdyny, 成绩: 120

public class Demo03JDBCUtil {
    @Test
    public void queryOne() {
       Connection connection=null;
       Statement statement=null;
       ResultSet resultSet=null;
       try {
           connection=JDBCUtil.getConnection();
           statement=connection.createStatement();
           /*String sql="insert into scores(sid,score,sname) values (4,120,'wdyny') ";
           int i=statement.executeUpdate(sql);
           if(i>0){
               System.out.println("执行成功");
           }else {
               System.out.println("执行失败");
           }*/
           String sql1="select * from scores";
           resultSet=statement.executeQuery(sql1);
           while (resultSet.next()){
               Object sid=resultSet.getObject(1);
               Object score=resultSet.getObject(2);
               Object sname=resultSet.getObject(3);
               System.out.println("编号: "+sid+", 姓名: "+sname+", 成绩: "+score);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       }finally {
           JDBCUtil.release(connection,statement,resultSet);
       }
    }
}